package com.geekchique.runningbuddy.provider;

import java.util.Calendar;

import com.geekchique.runningbuddy.util.DBHelperTools;

public class JogsDBSQL {
	public static final int DATABASE_VERSION = 1;
    public static final String DATABASE_NAME = "runningBuddyDB1";

    public static final String JOGGING_ENTRIES_TABLE = "jog";
    public static final String ROUTE_TABLE = "route";
    public static final String ROUTE_POINT_TABLE = "routePoint";
    public static final String TIME_TABLE = "time";

    public static final String JOG_ID = "_id";
    public static final String JOG_NAME = "name";

    public static final String ROUTE_ID = "_id";
    public static final String ROUTE_JOGID = "_jogId"; //-foreign key from jog

    public static final String ROUTEPOINT_POINT_ID = "_id";
    public static final String ROUTEPOINT_ROUTE_ID = "_routePointId"; //-foreign key from routePoint -should really be routePointNo
    public static final String ROUTEPOINT_LONGITUDE = "longitude";
    public static final String ROUTEPOINT_LATITUDE = "latitude";

    public static final String TIME_ID = "_id";
    public static final String TIME_ROUTE_ID = "routeId"; //-foreign key from route
    public static final String TIME_ROUTE_POINT_ID = "routePointId"; //-foreign key from routePoint
    public static final String TIME_JOG_ID = "jogId"; //-foreign key from jog
    public static final String TIME_TIME = "time";
    public static final String TIME_LAP = "lap";


    //All Results come back in the following results table
    public static final int JOGRESULTS_JOG_ID_COL = 0;
    public static final int JOGRESULTS_NAME_COL = 1;
    public static final int JOGRESULTS_ROUTE_ID_COL = 2;
    public static final int JOGRESULTS_POINT_NO_COL = 3;
    public static final int JOGRESULTS_LONGITUDE_COL = 4;
    public static final int JOGRESULTS_LATITUDE_COL = 5;
    public static final int JOGRESULTS_TIME_COL = 6;
    public static final int JOGRESULTS_LAP_COL = 7;


    /*
     * Database Layout
     *
     * jog - Stores each jog entry
     * route - Each jog has a route associated with it.
     * routePoint - Each route is made from stored points from the journey
     * time - Times are are associated with points on route during a jog
     *
     * */

    public static final String CREATE_JOG_TABLE = "CREATE TABLE jog (_id INTEGER PRIMARY KEY, name TEXT);";

    public static final String CREATE_ROUTE_TABLE = "CREATE TABLE route (_id INTEGER PRIMARY KEY, _jogId INTEGER);";

    public static final String CREATE_ROUTE_POINT_TABLE = "CREATE TABLE routePoint (_id INTEGER PRIMARY KEY, pointNo NUMERIC, _routeId INTEGER, latitude NUMERIC, longitude NUMERIC);";

    public static final String CREATE_TIME_TABLE = "CREATE TABLE time (_id INTEGER PRIMARY KEY, jogId NUMERIC, routePointId NUMERIC, routeId NUMERIC, lap BOOLEAN, time DATETIME);";

    // END-DB STRUCTURE


    public static String[] COLUMNS_IN_JOG_ENTRY = new String[] {
		JOG_ID,
	    JOG_NAME
	};

    public static String[] COLUMNS_IN_ROUTE_ENTRY = new String[] {
    	ROUTE_ID,
    	ROUTE_JOGID
    };

    public static String[] COLUMNS_IN_ROUTEPOINT_ENTRY = new String[] {
    	ROUTEPOINT_POINT_ID,
    	ROUTEPOINT_ROUTE_ID,
    	ROUTEPOINT_LONGITUDE,
    	ROUTEPOINT_LATITUDE
    };

    public static String[] COLUMNS_IN_TIME_ENTRY = new String[] {
    	TIME_ID,
    	TIME_ROUTE_ID,
    	TIME_ROUTE_POINT_ID,
    	TIME_JOG_ID,
    	TIME_TIME,
    	TIME_LAP
    };



    public static final String GET_LAST_ROUTE_POINT_FROM_JOG =
    	"SELECT time FROM time"
    	+"WHERE _routeId = ("
    	+ "					SELECT _id FROM route "
    	+ "					WHERE _joId = 1 "
    	+ "			)"
    	+ "			ORDER BY time DESC LIMIT 1;";

    public static final String GET_ALL_JOG_ENTRIES =
    	"SELECT jog._id, jog.name, jogStartTimes.startTime, jogStopTimes.stopTime "
    	+"FROM 	jog "
    	+"INNER JOIN "
    	+"( "
    	+"	SELECT DISTINCT startTimes.jogId AS _jogId, startTimes.time AS startTime "
    	+"	FROM 	time startTimes "
    	+"	WHERE 	startTimes.time = ( "
    	+"			SELECT	jog.time "
    	+"			FROM 	time jog "
    	+"			WHERE 	jog.jogId = startTimes.jogId "
    	+"			ORDER BY jog.routePointId ASC "
    	+"			LIMIT 1 "
    	+"			) "
    	+") jogStartTimes "
    	+"ON jog._id = jogStartTimes._jogId "
    	+"INNER JOIN "
    	+"( "
    	+"	SELECT DISTINCT stopTimes.jogId AS _jogId, stopTimes.time AS stopTime "
    	+"	FROM 	time stopTimes "
    	+"	WHERE 	stopTimes.time = "
    	+"		( "
    	+"			SELECT	jog.time "
    	+"			FROM 	time jog "
    	+"			WHERE 	jog.jogId = stopTimes.jogId "
    	+"			ORDER BY jog.routePointId DESC "
    	+"			LIMIT 1 "
    	+"		) "
    	+") jogStopTimes "
    	+ "ON jog._id = jogStopTimes._jogId";

    public static final String FETCH_ALL_TODAYS_JOG_ENTRY_START_STOP_TIMES =
    	 "SELECT jog._id, jog.name, jogStartTimes.startTime, jogStopTimes.stopTime "
    	+"FROM 	jog "
    	+"INNER JOIN "
    	+"( "
    	+"	SELECT DISTINCT startTimes.jogId AS _jogId, startTimes.time AS startTime "
    	+"	FROM 	time startTimes "
    	+"	WHERE 	startTimes.time = ( "
    	+"			SELECT	jog.time "
    	+"			FROM 	time jog "
    	+"			WHERE 	jog.jogId = startTimes.jogId "
    	+"			AND	strftime(\"%Y-%m-%d\", jog.time) = strftime(\"%Y-%m-%d\", 'now') "
    	+"			ORDER BY jog.routePointId ASC "
    	+"			LIMIT 1 "
    	+"			) "
    	+") jogStartTimes "
    	+"ON jog._id = jogStartTimes._jogId "
	    +"INNER JOIN "
	    +"( "
	    +"	SELECT DISTINCT stopTimes.jogId AS _jogId, stopTimes.time AS stopTime "
	    +"	FROM 	time stopTimes "
	    +"	WHERE 	stopTimes.time = "
	    +"		( "
	    +"			SELECT	jog.time "
	    +"			FROM 	time jog "
	    +"			WHERE 	jog.jogId = stopTimes.jogId "
	    +"			AND	strftime(\"%Y-%m-%d\", jog.time) = strftime(\"%Y-%m-%d\", 'now') "
	    +"			ORDER BY jog.routePointId DESC "
	    +"			LIMIT 1 "
	    +"		) "
	    +") jogStopTimes "
	    + "ON jog._id = jogStopTimes._jogId";

    public static String FETCH_ALL_TODAYS_JOG_ENTRIES =
    	  "SELECT DISTINCT jogdetails._jogId, jogdetails.name, route._id, routeandpoints.pointNo, routeandpoints.longitude, routeandpoints.latitude, timesroutesandpoints.time, timesroutesandpoints.lap "
    	+ "FROM route "
    	+ "INNER JOIN ("
    	+ "			SELECT routePoint._routeId AS _routeId, routePoint._id AS pointId, routePoint.pointNo AS pointNo, routePoint.latitude AS latitude, routePoint.longitude AS longitude "
    	+ "			FROM routePoint, route "
    	+ "			WHERE route._id = routePoint._routeId "
    	+ "		) routeandpoints "
    	+ "ON route._id = routeandpoints._routeId "
    	+ "INNER JOIN ("
    	+ "			SELECT time.lap AS lap, time.time AS time, time.routePointId AS routePointId "
    	+ "			FROM time, route "
    	+ "			WHERE route._id = time.routeId "
    	+ "			AND	strftime( \"%Y-%m-%d\", time.time ) = strftime( \"%Y-%m-%d\",\'now') "
    	+ "		) timesroutesandpoints "
    	+ "ON routeandpoints.pointId = timesroutesandpoints.routePointId "
    	+ "INNER JOIN ( "
    	+ "			SELECT jog._id AS _jogId, jog.name AS name "
    	+ "			FROM jog "
    	+ "		) jogdetails "
    	+ "ON route._jogId = jogdetails._jogId";

    public static String selectAJogEntry(String jogId){
    	return
    	"  SELECT DISTINCT jogdetails._jogId, jogdetails.name, route._id, routeandpoints.pointNo, routeandpoints.longitude, routeandpoints.latitude, timesroutesandpoints.time, timesroutesandpoints.lap "
    	+ "FROM route "
    	+ "INNER JOIN ("
    	+ "			SELECT routePoint._routeId AS _routeId, routePoint._id AS pointId, routePoint.pointNo AS pointNo, routePoint.latitude AS latitude, routePoint.longitude AS longitude "
    	+ "			FROM routePoint, route"
    	+ "			WHERE route._id = routePoint._routeId "
    	+ "			AND route._jogId = " + jogId
    	+ "		) routeandpoints "
    	+ "ON route._id = routeandpoints._routeId "
    	+ "INNER JOIN ("
    	+ "			SELECT time.lap AS lap, time.time AS time, time.routePointId AS routePointId "
    	+ "			FROM time, route "
    	+ "			WHERE route._id = time.routeId "
    	+ "			AND route._jogId = " + jogId
    	+ "		) timesroutesandpoints "
    	+ "ON routeandpoints.pointId = timesroutesandpoints.routePointId "
    	+ "INNER JOIN ("
    	+ "			SELECT jog._id AS _jogId, jog.name AS name "
    	+ "			FROM jog"
    	+ "			WHERE jog._id = " + jogId
    	+ "		) jogdetails "
    	+ "ON route._jogId = jogdetails._jogId";
    }

    public static String FETCH_STUBBEDOUT_JOG_ENTRIES =
  	  "SELECT DISTINCT jogdetails._jogId, jogdetails.name, route._id, routeandpoints.pointNo, routeandpoints.longitude, routeandpoints.latitude, timesroutesandpoints.time, timesroutesandpoints.lap"
  	+ "FROM route "
  	+ "INNER JOIN ("
  	+ "			SELECT routePoint._routeId AS _routeId, routePoint._id AS pointId, routePoint.pointNo AS pointNo, routePoint.latitude AS latitude, routePoint.longitude AS longitude"
  	+ "			FROM routePoint, route"
  	+ "			WHERE route._id = routePoint._routeId "
  	+ "		) routeandpoints "
  	+ "ON route._id = routeandpoints._routeId "
  	+ "INNER JOIN ("
  	+ "			SELECT time.lap AS lap, time.time AS time, time.routePointId AS routePointId"
  	+ "			FROM time, route"
  	+ "			WHERE route._id = time.routeId "
  	+ "			AND	strftime( \"%Y-%m-%d\", time.time ) = strftime( \"%Y-%m-%d\",\'2008-03-10T10:00:00:00.000') "
  	+ "		) timesroutesandpoints "
  	+ "ON routeandpoints.pointId = timesroutesandpoints.routePointId "
  	+ "INNER JOIN ("
  	+ "			SELECT jog._id AS _jogId, jog.name AS name"
  	+ "			FROM jog"
  	+ "		) jogdetails "
  	+ "ON route._jogId = jogdetails._jogId";

    public static String getJogId(String jogName){
    	return "SELECT _id "
    	+ "FROM jog "
    	+ "WHERE name = " + jogName
    	+ "LIMIT 1";
    }

    public static String insertJogName(String jogName){
    	return "INSERT INTO jog (name) VALUES ( \'"+ jogName + "\')";
    }

    public static String selectJustEnteredJogId(){
    	return "SELECT _id "
    	+ "FROM jog "
    	+ "ORDER BY _id DESC "
    	+ "LIMIT 1";
    }

    public static String insertRouteJogId(String jogId){
    	return "INSERT INTO route (_jogId) "
    	+ "VALUES ( \'" + jogId + "\');";
    }

    public static String selectJustEnteredRouteId(String jogId){
    	return "SELECT _id "
    	+ "FROM route "
    	+ "WHERE _jogId = " + jogId
    	+ " ORDER BY _id DESC "
    	+ "LIMIT 1";
    }

    public static String selectJustEnteredRoutePointId(String routeId){
    	return "SELECT _id FROM routePoint WHERE _routeId = " + routeId
    	+ " ORDER BY _id DESC LIMIT 1";
    }

    public static String insertRoutePoint(String pointNo, String _routeId, String latitude, String longitude){
    	return "INSERT INTO routePoint (pointNo, _routeId, latitude, longitude ) VALUES ( \'"
    	+ pointNo + "\',\'" + _routeId + "\',\'" + latitude + "\',\'" + longitude + "\')";
    }

    public static String addATimeEntry(String jogId, String routeId, String routePointId, String time, String lap){
    	return
    	"INSERT INTO time (jogId, routePointId, routeId, time, lap) "
    	+ "VALUES (\'" + jogId + "\',\'" + routePointId + "\',\'" + routeId + "\',\'" + time + "\',\'" + lap + "\');";
    }

    public static final String WHERE_ENTRIES_WERE_TODAY = "strftime( \"%Y-%m-%d\"," + TIME_TIME + ") = strftime( \"%Y-%m-%d\",'now');";

    public static final String WHERE_ENTRIES_WERE_THIS_MONTH = "strftime( \"%Y-%m\"," + TIME_TIME + ") = strftime( \"%Y-%m\",'now');";


	public static String whereEntriesWereThisMonth(Calendar calendar) {
		String query = 	"strftime( \"%Y-%m\"," + TIME_TIME + ") = strftime( \"%Y-%m\",'" + DBHelperTools.expectedFormat(calendar.getTimeInMillis())	+ "');";

		return query;
	}

	//Entries must be entered into DB in YYYY-MM-DDTHH:MM:SS.SSS format
	public static String getWhereCondition(Integer day, Integer month, Integer year) {
		String  dayString;
		String monthString;

		if(day < 10){
			dayString = "0" + day.toString();
		}else{
			dayString = day.toString();
		}

		if(month < 10){
			monthString = "0" + month.toString();
		}else{
			monthString = month.toString();
		}

		return "strftime( \"%Y-%m-%d\"," + TIME_TIME + ") = strftime( \"%Y-%m-%d\",\"" + year.toString() + "-"+ monthString + "-" + dayString +"T12:00:00.000\");";
	}

	public static String GET_ENTRIES(Integer day, Integer month, Integer year) {

		String  dayString;
		String monthString;

		if(day < 10){
			dayString = "0" + day.toString();
		}else{
			dayString = day.toString();
		}

		if(month < 10){
			monthString = "0" + month.toString();
		}else{
			monthString = month.toString();
		}


		String query =
   	 "SELECT DISTINCT jogdetails._jogId, jogdetails.name, route._id, routeandpoints.pointNo, routeandpoints.longitude, routeandpoints.latitude, timesroutesandpoints.time, timesroutesandpoints.lap "
 	+ "FROM route "
 	+ "INNER JOIN ("
 	+ "			SELECT routePoint._routeId AS _routeId, routePoint._id AS pointId, routePoint.pointNo AS pointNo, routePoint.latitude AS latitude, routePoint.longitude AS longitude "
 	+ "			FROM routePoint, route "
 	+ "			WHERE route._id = routePoint._routeId "
 	+ "		) routeandpoints "
 	+ "ON route._id = routeandpoints._routeId "
 	+ "INNER JOIN ("
 	+ "			SELECT time.lap AS lap, time.time AS time, time.routePointId AS routePointId "
 	+ "			FROM time, route "
 	+ "			WHERE route._id = time.routeId "
 	+ "			AND	strftime( \"%Y-%m-%d\"," + TIME_TIME + ") = strftime( \"%Y-%m-%d\",\'" + year.toString() + "-"+ monthString + "-" + dayString +"T12:00:00.000\') "
 	+ "		) timesroutesandpoints "
 	+ "ON routeandpoints.pointId = timesroutesandpoints.routePointId "
 	+ "INNER JOIN ( "
 	+ "			SELECT jog._id AS _jogId, jog.name AS name "
 	+ "			FROM jog "
 	+ "		) jogdetails "
 	+ "ON route._jogId = jogdetails._jogId";

   	 return query;

	}

	public static String GET_MONTH_ENTRIES(Integer month, Integer year) {

		String monthString;


		if(month < 10){
			monthString = "0" + month.toString();
		}else{
			monthString = month.toString();
		}


		String query =
			"SELECT DISTINCT jogdetails._jogId, jogdetails.name, route._id, routeandpoints.pointNo, routeandpoints.longitude, routeandpoints.latitude, timesroutesandpoints.time, timesroutesandpoints.lap "
			+ "FROM route "
			+ "INNER JOIN ("
			+ "			SELECT routePoint._routeId AS _routeId, routePoint._id AS pointId, routePoint.pointNo AS pointNo, routePoint.latitude AS latitude, routePoint.longitude AS longitude "
			+ "			FROM routePoint, route "
			+ "			WHERE route._id = routePoint._routeId "
			+ "		) routeandpoints "
			+ "ON route._id = routeandpoints._routeId "
			+ "INNER JOIN ("
			+ "			SELECT time.lap AS lap, time.time AS time, time.routePointId AS routePointId "
			+ "			FROM time, route "
			+ "			WHERE route._id = time.routeId "
			+ "			AND	strftime( \"%Y-%m\"," + TIME_TIME + ") = strftime( \"%Y-%m\",\'" + year.toString() + "-"+ monthString + "-01T12:00:00.000\') "
			+ "		) timesroutesandpoints "
			+ "ON routeandpoints.pointId = timesroutesandpoints.routePointId "
			+ "INNER JOIN ( "
			+ "			SELECT jog._id AS _jogId, jog.name AS name "
			+ "			FROM jog "
			+ "		) jogdetails "
			+ "ON route._jogId = jogdetails._jogId";

		return query;

	}


}
